from copy import deepcopy
from .exceptions import CallBackError
from .handler_func import (get_sql, get_add_unique_sql, get_create_table_sql, id_exists, handle_values, commit,
                           error_back, rollback, judge_type, get_create_table_keys, get_create_table_values,
                           get_create_new_fields_sql, is_dict_or_scrapyItem, get_update_fields_length_sql)


class MysqlHandlerBase(object):

    def __init__(self, pool):
        self._pool = pool

    def _set_list(self, item):
        item = deepcopy(item)
        return [item]

    def _check(self, item):
        if is_dict_or_scrapyItem(item):
            item = self._set_list(item)
        judge_type(item)
        id_exists(item)
        return item

    def _process(self, cursor, item, table_name, create, unique, typ, db=None):
        create_table_keys = get_create_table_keys(item)                         # 获取到打乱后的key的列表
        create_table_values = get_create_table_values(create_table_keys, item)  # 传入打乱后的key的列表

        sql_execute = cursor.executemany
        sql = get_sql(create_table_keys, table_name, typ)
        insert_values = handle_values(create_table_keys, item)
        try:
            sql_execute(sql, insert_values)
            commit(db)
        except Exception as e:
            code = e.args[0]
            create_table_sql = None
            add_unique_sql = None
            create_new_fields_sql = None
            update_fields_length_sqls = None
            if int(code) == 1146:           # code为1146 判断是否需要创建表
                if not create:
                    rollback(db)
                else:
                    create_table_sql = get_create_table_sql(table_name, create_table_keys, create_table_values)
                    if unique:
                        add_unique_sql = get_add_unique_sql(table_name, unique)
            elif int(code) == 1054:         # code为1054 需要创建新的字段
                create_new_fields_sql = get_create_new_fields_sql(table_name, item, cursor)
            elif int(code) == 1406:         # code为1006 需要更改字段的长度
                update_fields_length_sqls = get_update_fields_length_sql(table_name, item, cursor)
            else:
                rollback(db)
            try:
                self._execute_one_sql(cursor, create_table_sql)
                self._execute_one_sql(cursor, add_unique_sql)
                self._execute_one_sql(cursor, create_new_fields_sql)
                if update_fields_length_sqls:
                    for update_fields_length_sql in update_fields_length_sqls:
                        self._execute_one_sql(cursor, update_fields_length_sql)
                sql_execute(sql, insert_values)
                commit(db)
            except Exception as e:
                code = int(e.args[0])
                if code == 1406:
                    try:
                        update_fields_length_sqls = get_update_fields_length_sql(table_name, item, cursor)
                        for update_fields_length_sql in update_fields_length_sqls:
                            self._execute_one_sql(cursor, update_fields_length_sql)
                        sql_execute(sql, insert_values)
                        commit(db)
                    except:
                        rollback(db)
                else:
                    rollback(db)

    def _execute_one_sql(self, cursor, sql):
        if sql:
            cursor.execute(sql)

    def close(self):
        '''
        关闭所有链接
        '''
        self._pool.close()


class DBUtilSyncMysqlHandler(MysqlHandlerBase):

    def insert(self, item, table_name, create=True, unique=None):
        '''
        :param pool:            代理池对象
        :param item:            要插入的数据，一条(字典 or Scrapy.Item)，多条(列表 or 元组)
        :param table_name:      要插入的表明
        :param create:          是否创建表，默认创建
        :param unique:          是否创建唯一索引，一个或多个(列表)
        '''
        connection = self._pool.connection()
        self._middle_handler(connection, item, table_name, create, unique, typ='insert')

    def upsert(self, item, table_name, create=True, unique=None):
        connection = self._pool.connection()
        self._middle_handler(connection, item, table_name, create, unique, typ='upsert')

    def _middle_handler(self, pool, item, table_name, create, unique, typ):
        item = self._check(item)
        cursor = pool.cursor()
        self._process(cursor=cursor, item=item, table_name=table_name, create=create, unique=unique, typ=typ, db=pool)


class TwistedAsyncMysqlHandler(MysqlHandlerBase):

    def __init__(self, pool, errback):
        super().__init__(pool)
        self.errback = errback or error_back

    def insert(self, item, table_name, create=True, unique=None):
        '''
        :param pool:            代理池对象
        :param item:            要插入的数据，一条(字典 or Scrapy.Item)，多条(列表 or 元组)
        :param table_name:      要插入的表明
        :param create:          是否创建表，默认创建
        :param unique:          是否创建唯一索引，一个或多个(列表)
        '''
        self._middle_handler(self._pool, item, table_name, create, unique, typ='insert')

    def upsert(self, item, table_name, create=True, unique=None):
        self._middle_handler(self._pool, item, table_name, create, unique, typ='upsert')

    def _middle_handler(self, pool, item, table_name, create, unique, typ):
        if not callable(self.errback):
            raise CallBackError('传入的{}不是一个可执行函数'.format(self.errback.__name__))
        item = self._check(item)
        deferred = pool.runInteraction(self._process, item, table_name, create, unique, typ)
        deferred.addErrback(self.errback, item)




